*******************************************************************************
*``The Costs and Environmental Justice Concerns of NIMBY in Solid Waste Disposal''*
* Phuong Ho (SNF-Centre for Applied Research in Norwegian School of Economics) *
*******************************************************************************
* This file makes Table 3 in main text and Tables D1-D7, D9-D10 in Appendix D **
cls
clear all 
capture log close
set more off
global mydir "C:\XYZ" // set working directory
global data "$mydir\data"
global matlabin "$mydir\matlab\input"
global result "$mydir\TEX\"
cd "$mydir\stata"
set matsize 11000
set maxvar 32700

**********************************************************************
************ MAKE TABLES OF COUNTERFACTUALS *********************************
******************* TABLES 3 ***************************************
****************************************************************************
***0. SELECT THE COUNTERFACTUAL *******************************************
local scenario "importBan importTax" 
local cnty "Marin Solano" // Marin Solano"
**************************************************************************
**** BUILD THE SAMPLE. IMPORT STATUS QUO ESTIMATES **************************
***************************************************************************
import excel "$mydir\matlab\output\nimby_statusquo.xlsx", sheet("Sheet1") firstrow clear
label var sj_q "market size"
label var pr "model implied market share"
label var Bq "model implied trash amount"
label var el "price elasticity"
label var CS "hauler's cost"
sort TCJ
save nimby_counterfactual, replace

**************************************************************************
****** IMPORT DEMOGRAPHICS IN 3-MILE COMMUNITIES *************************
***************************************************************************
// 1/3. Import demographics for county of origin 
use $data\demographics\raw\county_demographics_2010_CA, clear
keep *geoid *name *pop *white* *black* *hispanic* *asian* *mdhhinc
foreach x of varlist _all {
	rename `x' O_`x'
} 
rename O_COname county
gen year=2010
sort year county
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear
sort year county
merge m:1 year county using `tmp'
erase `tmp'
drop if _merge==2 //_merge==1 are not 2010, _merge==2 are counties that doesn't generate trash in 2010
drop _merge
replace O_COmdhhinc=O_COmdhhinc*index2000
save nimby_counterfactual, replace

// 2/3. Import demographics for county of destination 
use $data\demographics\raw\county_demographics_2010_CA, clear
keep *geoid *name *pop *white* *black* *hispanic* *asian* *mdhhinc
foreach x of varlist _all {
	rename `x' D_`x'
} 
rename D_COname descnty
gen year=2010
sort year descnty
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear
sort year descnty
merge m:1 year descnty using `tmp'
erase `tmp'
*list facility descnty if _merge==1 //must be oos
*list  year county facility descnty if _merge==2 // San Francisco, Alpine, Nevada (don't have any landfills) Yuba and Sutter since both merge
drop if _merge==2 //_merge==1 are not 2010, _merge==2 are counties that doesn't generate trash in 2010
drop _merge
replace D_COmdhhinc=D_COmdhhinc*index2000
save nimby_counterfactual, replace

// 3/3. Import demographics for community of destination
use $data\buffer\demographics_buffer_3mile, clear
keep code year Bpop B0pop Bwhite Bblack Basian Bhispanic Bmdhhinc
local varl "white black asian hispanic"
foreach v in `varl' {
  gen B`v'_p=B`v'*100/Bpop
}
foreach x of varlist _all {
	rename `x' D_`x'
} 
rename D_code Code
rename D_year year
sort year Code
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear
sort year Code
merge m:1 year Code using `tmp'
erase `tmp'
*list facility descnty if _merge==1 //must be oos
*list  year county facility descnty if _merge==2 // San Francisco, Alpine, Nevada (don't have any landfills) Yuba and Sutter since both merge
drop if _merge==2 //_merge==1 must be 0 if analyzing at other years, _merge==2 are counties that doesn't generate trash in 2010
drop _merge
replace D_Bmdhhinc=D_Bmdhhinc*index2000 if year==2010  // adjust income to 2000 dollars
save nimby_counterfactual, replace
****************************************************************************
**** NOW, DIFFERENT SCENARIO DOES DIFFERENT CODE ****************************

use nimby_counterfactual, clear
bys year Code: gen dupj=cond(_N==1,0,_n)
bys year county: gen dupc=cond(_N==1,0,_n)
bys year descnty: gen dupd=cond(_N==1,0,_n)
bys year quarter county: gen duptc=cond(_N==1,0,_n)
save nimby_counterfactual, replace
use nimby_counterfactual, clear 

************** RESULTS OF EQUILIBRIUM MODEL ****************************
foreach c in `cnty' {
foreach s in `scenario' {
	if "`s'"=="importBan" {
****1. IMPORT DATA OF COUNTERFACTUAL SCENARIO ***********
import excel "$mydir\matlab\output\nimby_counterfactual_`s'_with_eqprice_`c'.xlsx", sheet("Sheet1") firstrow clear
keep year quarter county Code Cpr Cq CCS Crealprice
sort year quarter county Code
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear 

sort year county
by year county: egen slocal=total(localf)
replace slocal=1 if slocal>0 & slocal<. // whether a county has a local facility
rename slocal havef

bys yq county: egen slocal=total(localf)
replace slocal=1 if slocal>0 & slocal<.
*drop if slocal==0 & havef==1
********************back to original code below ******
sort year quarter county Code
merge 1:1 year quarter county Code using `tmp', force
drop _merge
erase `tmp'

bys year Code: egen Crealprice_del=mean(Crealprice)
drop Crealprice
rename Crealprice_del Crealprice
gen Nrealprice=Crealprice
gen Ndridis=dridis
*spread values because I drop some observations when doing counterfactuals
bys year quarter county: egen CCS_del=mean(CCS)
drop CCS
rename CCS_del CCS

gen DEL_q=Cq-Bq
gen Nq=q+DEL_q/Bq*q
replace Nq=0 if DEL_q<=1e-2 & q==0
replace Nq=Cq-Bq if DEL_q>1e-2 & q==0
replace Nq=0 if localf==0
gen Dq=Nq-q

******************  ECONOMIC VARIABLES *********************
*** Calculate policy variables at current state (with actual q)
keep if year==2010
bys year county: egen havelocalc=total(localf)
replace havelocalc=1 if havelocalc>0 & havelocalc<.
******************** CURRENT STATISTICS. ACTUAL LEVEL *****************
**calculate export amount, import amount, and net import by county
sort year county  // exports
by year county: egen Atcq=total(q)
*export to other county, but within California
by year county: egen Aexpq_del= total(q) if localf==0 & j~="oos" 
by year county: egen Aexpq= mean(Aexpq_del)
replace Aexpq=0 if Aexpq==.
sort year desco // imports
*import by destination county
by year desco: egen Aimpdes_del =total(q) if localf==0 & j~="oos" 
by year desco: egen Aimpdes=mean(Aimpdes_del) // spread values to des cnty level
replace Aimpdes=0 if Aimpdes==.
gen Aimpq_del= Aimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimpq=mean(Aimpq_del) // spread values to county
replace Aimpq=0 if Aimpq==.
drop *_del Aimpdes
gen Anetim= Aimpq - Aexpq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen Arqdes_del=total(q)
gen Arq_del=Arqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Arq=mean(Arq_del)
drop *_del

**payment, total tipping fees
sort year county // exports
gen Adol_del=q*realprice
by year county: egen Acdol=total(Adol_del) // total tipping fees
replace Acdol=0 if Acdol==.
by year county: egen Aexdol_c_del=total(Adol_del) if localf==0 & j~="oos"
by year county: egen Aexdol=mean(Aexdol_c_del)
replace Aexdol=0 if Aexdol==.
sort year desco // imports
by year desco: egen Aimdol_d_del=total(Adol_del) if localf==0 & j~="oos"
by year desco: egen Aimdold=mean(Aimdol_d_del)
replace Aimdold=0 if Aimdold==.
gen Aimdol_c_del=Aimdold if county==desco // spread values from des cnty to cnty
sort year county
by year county: egen Aimdol=mean(Aimdol_c_del)
replace Aimdol=0 if Aimdol==.
gen Anetimdol=Aimdol-Aexdol
drop *_del
gen Acdolpt=Acdol/Atcq // dollar per ton

**trash miles
gen Acmil_del=q*dridis
sort year county
by year county: egen Acmil=total(Acmil_del) // total trash ton-miles
replace Acmil=0 if Acmil==.
drop *_del
gen Acmilpt=Acmil/Atcq // mile per ton

******************* SIMULATED/MODEL IMPLIED STATISTICS ****************
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen tcq=total(Bq)
by year county: egen gq_del=total(Bq) if j~="oos"
by year county: egen gq=mean(gq_del)
*export to other county, but within California
by year county: egen expq_del= total(Bq) if localf==0 & j~="oos"
by year county: egen expq= mean(expq_del)
replace expq=0 if expq==.
sort year desco //imports
*import by destination county
by year desco: egen impdes_del =total(Bq) if localf==0 & j~="oos" 
by year desco: egen impdes=mean(impdes_del) // spread values to des cnty level
replace impdes=0 if impdes==.
gen impq_del= impdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen impq=mean(impq_del) // spread values to county
replace impq=0 if impq==.
drop *_del impdes
gen netim= impq - expq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen rqdes_del=total(Bq)
gen rq_del=rqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen rq=mean(rq_del)
replace rq=0 if rq==.
drop *_del

**payment, total tipping fees
sort year county //exports
gen dol_del=Bq*realprice
by year county: egen cdol=total(dol_del) // total tipping fees
replace cdol=0 if cdol==.
by year county: egen exdol_c_del=total(dol_del) if localf==0 & j~="oos"
by year county: egen exdol=mean(exdol_c_del)
replace exdol=0 if exdol==.
sort year desco //imports
by year desco: egen imdol_d_del=total(dol_del) if localf==0 & j~="oos"
by year desco: egen imdold=mean(imdol_d_del)
replace imdold=0 if imdold==.
gen imdol_c_del=imdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen imdol=mean(imdol_c_del)
replace imdol=0 if imdol==.
gen netimdol=imdol-exdol
drop*_del
gen cdolpt=cdol/tcq // dollar per ton
gen exdolpt=exdol/expq // dollar per ton for exports

*trash miles
gen cmil_del=Bq*dridis
sort year county
by year county: egen cmil=total(cmil_del) // total trash ton-miles
replace cmil=0 if cmil==.  // trash mileage
by year county: egen exmil_c_del=total(cmil_del) if localf==0 & j~="oos"
by year county: egen exmil=mean(exmil_c_del)
replace exmil=0 if exmil==.
drop *_del
gen cmilpt=cmil/tcq // mile per trip
gen exmilpt=exmil/expq // mile per trip for exports

********************  NEW LEVELS ********************
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen Ntcq=total(Cq)
by year county: egen Ngq_del=total(Cq) if j~="oos"
by year county: egen Ngq=mean(Ngq_del)
by year county: egen Nexpq_del= total(Cq) if localf==0 & j~="oos"
by year county: egen Nexpq= mean(Nexpq_del)
replace Nexpq=0 if Nexpq==.
sort year desco //imports
by year desco: egen Nimpdes_del =total(Cq) if localf==0 & j~="oos" 
by year desco: egen Nimpdes=mean(Nimpdes_del) // spread values to des cnty level
replace Nimpdes=0 if Nimpdes==.
gen Nimpq_del= Nimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Nimpq=mean(Nimpq_del) // spread values to county
replace Nimpq=0 if Nimpq==.
drop *_del Nimpdes
gen Nnetim= Nimpq - Nexpq

**total receiving trash
sort year desco
by year desco: egen Nrqdes_del=total(Cq)
gen Nrq_del=Nrqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Nrq=mean(Nrq_del)
replace Nrq=0 if Nrq==.
drop *_del

*payment
sort year county //exports
gen Ndol_del=Cq*Nrealprice
by year county: egen Ncdol=total(Ndol_del)
replace Ncdol=0 if Ncdol==.
by year county: egen Nexdol_c_del=total(Ndol_del) if localf==0 & j~="oos"
by year county: egen Nexdol=mean(Nexdol_c_del)
replace Nexdol=0 if Nexdol==.
sort year desco //imports
by year desco: egen Nimdol_d_del=total(Ndol_del) if localf==0 & j~="oos"
by year desco: egen Nimdold=mean(Nimdol_d_del)
replace Nimdold=0 if Nimdold==.
gen Nimdol_c_del=Nimdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen Nimdol=mean(Nimdol_c_del)
replace Nimdol=0 if Nimdol==.
gen Nnetimdol=Nimdol-Nexdol
gen Ncdolpt=Ncdol/Ntcq // dollar per ton
drop *_del
gen Nexdolpt=Nexdol/Nexpq // dollar per ton for exports

*trash miles
gen Ncmil_del=Cq*Ndridis
sort year county
by year county: egen Ncmil=total(Ncmil_del)
replace Ncmil=0 if Ncmil==.
gen Ncmilpt=Ncmil/tcq // mile per trip
by year county: egen Nexmil_c_del=total(Ncmil_del) if localf==0 & j~="oos"
by year county: egen Nexmil=mean(Nexmil_c_del)
replace Nexmil=0 if Nexmil==.
drop *_del
gen Nexmilpt=Nexmil/Nexpq // mile per trip for exports

sort year quarter county
by year quarter county: egen cntybq=total(Bq)
by year quarter county: egen cntycq=total(Cq)
gen totCS=CS*cntybq
gen NtotCS=CCS*cntycq
*gen ADtotCS=NtotCS-totCS
sort year county quarter
by year county quarter: gen dup=cond(_N==1,0,_n)
by year county: egen ctotCS_del=total(totCS) if dup<=1
by year county: egen ctotCS=mean(ctotCS_del)
by year county: egen NctotCS_del=total(NtotCS) if  dup<=1
by year county: egen NctotCS=mean(NctotCS_del) 
gen ctotCSpt=ctotCS/tcq // dollar per ton
gen NctotCSpt=NctotCS/Ntcq // dollar per ton
drop *_del
gen expqp = expq*100/tcq // percentage of export
*gen Nexpqp= Nexpqp*100/tcq
gen impqp = impq*100/tcq
******** change in external cost of transportation ******
*Austin2018 estimates external cost of truck trans=2.62-5.86 in 2014 cents per ton-mile
*This means 1.91-4.26 in 2000 cents per ton-mile
gen extran1=cmil*1.91/100 // in dollar
gen Nextran1=Ncmil*1.91/100 // in dollar

*total waste to county of policy
bys year county: egen qto`c'_del=total(Bq) if desco=="`c'"
bys year county: egen qto`c'=mean(qto`c'_del)
replace qto`c'=0 if qto`c'==.
bys year county: egen Nqto`c'_del=total(Cq) if desco=="`c'"
bys year county: egen Nqto`c'=mean(Nqto`c'_del)
replace Nqto`c'=0 if Nqto`c'==. 
drop *_del
replace Nqto`c'=. if qto`c'==0 & Nqto`c'==0
replace qto`c'=. if qto`c'==0 & Nqto`c'==.

local varl "rq expq impq cdol cmil ctotCS extran1 qto`c'"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}

by year: egen atcq=mean(tcq) if dupc<=1 & havelocalc==1 & county~="`c'"

local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'pt=AD`v'/atcq
}

replace ADextran1=0 if abs(ADcmilpt)<0.0005
replace ADcmilpt=0 if abs(ADcmilpt)<0.0005

**change by county ******
local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'ptc=AD`v'/tcq
}

***benefits of reduction in export to break even with the hauling cost ***
*each ycounty has importq impq and cost ctotCS
gen hhvaluec=-ADctotCS/ADimpq

******* change in price at facility level ***************
egen tagfq=tag(year quarter Code)
bys year Code: egen lanpri_del=mean(realprice) if tagfq==1
bys year Code: egen lanpri=mean(lanpri_del)
bys year Code: egen Clanpri_del=mean(Crealprice) if tagfq==1
bys year Code: egen Clanpri=mean(Clanpri_del)
bys year Code: egen Nlanpri_del=mean(Nrealprice) if tagfq==1
bys year Code: egen Nlanpri=mean(Nlanpri_del)
drop *_del

** change in price at facilities at the county (desco)
bys year desco: egen avgpricec_del=mean(lanpri)
gen avgpricec_del2=avgpricec_del if county==desco
bys year county: egen avgpricec=mean(avgpricec_del2)
drop *_del*
bys year desco: egen Navgpricec_del=mean(Clanpri) 
gen Navgpricec_del2=Navgpricec_del if county==desco
bys year county: egen Navgpricec=mean(Navgpricec_del2)
drop *_del*

*tag facility in counties that satisfy conditions
egen tagf=tag(year Code) 

by year: egen avgprice=mean(lanpri) if tagf==1
by year: egen Navgprice=mean(Clanpri) if tagf==1

bys year Code: egen lanq=total(Bq) // total waste at a facility
by year: egen avglanq=mean(lanq) if tagf==1
bys year Code: egen Nlanq=total(Cq)
by year: egen Navglanq=mean(Nlanq) if tagf==1

*There may be difference change bw facility that received foreign flow vs local
*tag facility that received intercounty flow before policy.
egen tagff=tag(year Code) if localf==0 & q>0
bys year Code: egen havefq=total(tagff)

by year: egen avgpriceother_del=mean(lanpri) if tagf==1 & desco~="`c'"
by year: egen avgpriceother=mean(avgpriceother_del)
by year: egen Cavgpriceother_del=mean(Clanpri) if tagf==1 & desco~="`c'"
by year: egen Cavgpriceother=mean(Cavgpriceother_del)
by year: egen Navgpriceother_del=mean(Nlanpri) if tagf==1 & desco~="`c'"
by year: egen Navgpriceother=mean(Navgpriceother_del)
gen ADCavgpriceother=Cavgpriceother - avgpriceother
gen ADNavgpriceother=Navgpriceother - avgpriceother // including tax, if any

by year: egen avgpricef_del=mean(lanpri) if havefq==1 & tagf==1 & desco~="`c'"
by year: egen avgpricef=mean(avgpricef_del)
by year: egen Navgpricef_del=mean(Clanpri) if havefq==1 & tagf==1 & desco~="`c'"
by year: egen Navgpricef=mean(Navgpricef_del)
by year: egen avgpricel_del=mean(lanpri) if havefq==0 & tagf==1 & desco~="`c'"
by year: egen avgpricel=mean(avgpricel_del)
by year: egen Navgpricel_del=mean(Clanpri) if havefq==0 & tagf==1 & desco~="`c'"
by year: egen Navgpricel=mean(Navgpricel_del)

by year: egen avgpricef`c'_del=mean(lanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen avgpricef`c'=mean(avgpricef`c'_del)
by year: egen Navgpricef`c'_del=mean(Clanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen Navgpricef`c'=mean(Navgpricef`c'_del)
by year: egen avgpricel`c'_del=mean(lanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen avgpricel`c'=mean(avgpricel`c'_del)
by year: egen Navgpricel`c'_del=mean(Clanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen Navgpricel`c'=mean(Navgpricel`c'_del)
*price including tax, if any
by year: egen Ntavgpricef`c'_del=mean(Nlanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen Ntavgpricef`c'=mean(Ntavgpricef`c'_del)
by year: egen Ntavgpricel`c'_del=mean(Nlanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen Ntavgpricel`c'=mean(Ntavgpricel`c'_del)
gen ADNavgpricef`c' = Ntavgpricef`c' - avgpricef`c' // including tax, if any
gen ADNavgpricel`c' = Ntavgpricel`c' - avgpricel`c' // including tax, if any
drop *_del

by year: egen avglanqf_del=mean(lanq) if havefq==1 & tagf==1
by year: egen avglanqf=mean(avglanqf_del)
by year: egen Navglanqf_del=mean(Nlanq) if havefq==1 & tagf==1
by year: egen Navglanqf=mean(Navglanqf_del)
by year: egen avglanql_del=mean(lanq) if havefq==0 & tagf==1
by year: egen avglanql=mean(avglanql_del)
by year: egen Navglanql_del=mean(Nlanq) if havefq==0 & tagf==1
by year: egen Navglanql=mean(Navglanql_del)
drop *_del

**Export to Marin
bys year: egen exto`c'_del=total(Bq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen exto`c'=mean(exto`c'_del) 
bys year: egen Nexto`c'_del=total(Cq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen Nexto`c'=mean(Nexto`c'_del)

**Total intercounty in CA
bys year: egen interq_del=total(Bq) if localf==0 & j~="oos"
bys year: egen interq=mean(interq_del)
bys year: egen Ninterq_del=total(Cq) if localf==0 & j~="oos"
bys year: egen Ninterq=mean(Ninterq_del)
drop *_del

local varl "exto`c' interq avgpricec avgprice avgpricef avgpricel avgpricef`c' avgpricel`c' avglanq avglanqf avglanql"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}

*******###############################################################********
* Notice ADctotCS shows change in cost
*******************************************************************************
di "This is the result of counterfactual (with price adjustment) of scenario:"
di "`s' in county `c'"
label var exto`c' "import in `c' (baseline)"
label var ADexto`c' "change in import in `c'"
label var interq "intercounty waste in CA"
label var ADinterq "change in intercounty waste in CA"
label var expqp "percentage of export (baseline)"
label var expq "export amount (baseline)"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount (baseline)"
label var ADimpq "change in import amount"
label var cdolpt "tipping fees (baseline)"
label var ADcdolptc "change in tipping fees"
label var cmilpt "mileage (baseline)"
label var ADcmilptc "change in mileage"
label var ADctotCSptc "change in discarding cost"
label var extran1 "external transport cost (baseline)"
label var ADextran1 "change in external transport cost"
label var hhvaluec "household value"
label var expqp "percentage of export (baseline)"
label var expq "export amount"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount"
label var ADimpq "change in import amount"

*******************  TABLE 3 or D5. Column (1): IMPORT BAN *******************
di "TABLE 3. Column (1): IMPORT BAN"
di "Or table D5, if this is about Solano County"
di "PANEL B: CHANGE IN THE COUNTY THAT IMPLEMENTS NIMBY POLICIES"
sum exto`c' ADexto`c' interq ADinterq expqp expq ADexpq impqp impq ADimpq ///
	cdolpt ADcdolptc cmilpt ADcmilptc ADctotCSptc ///
	extran1 ADextran1 hhvaluec ///
	if year==2010  & dupc<=1 & havelocalc==1 & county=="`c'"
di "PANEL C: CHANGE IN THE OTHER COUNTIES"
sum expqp expq ADexpq impqp impq ADimpq  ///
	cdolpt ADcdolptc cmilpt ADcmilptc ADctotCSptc ///
	extran1 ADextran1 hhvaluec ///
	if year==2010  & dupc<=1 & havelocalc==1 & county~="`c'"	
di "PANEL A: CHANGE AT FACILITY LEVEL"
sum avgprice ADavgprice ADavgpricep ///
	avgpricef`c' ADavgpricef`c' ADavgpricef`c'p ///
	avgpricel`c' ADavgpricel`c' ADavgpricel`c'p ///
	avgpricef ADavgpricef ADavgpricefp ///
	avgpricel ADavgpricel ADavgpricelp  ///
	ADNavgpricef`c' ADNavgpricel`c' ///
	avgpriceother ADCavgpriceother ADNavgpriceother if year==2010 & tagf==1


***********  TABLE D1 or D10 IN APPENDIX  ************************************
gen cpwlat`c'_del=cpwlat if county=="`c'"
egen cpwlat`c'=mean(cpwlat`c'_del)
gen cpwlon`c'_del=cpwlon if county=="`c'"
egen cpwlon`c'=mean(cpwlon`c'_del)
drop *_del

geodist cpwlat cpwlon cpwlat`c' cpwlon`c', miles gen(cdis)

gsort cdis county
di "TABLE D1 IN APPENDIX D: PANEL B"
di "Or table D6, if this is about Solano County"
list county gq rq ADrq ADexpq ADimpq ///
	ADextran1 ADcdolptc ADcmilptc ADctotCSptc hhvaluec cdis  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 
	
list county avgpricec ADavgpricec ADavgpricecp ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 

** DISTRIBUTIONAL EFFECTS. TABLE 6  ***************************************
keep if year==2010

*** percent of trash of origin county to its all within-60miles facilities
sort year county Code
by year county Code: gen yjdup=cond(_N==1,0,_n)
by year county Code: egen yjaq=total(Bq) // receiving q at year-county level
by year county Code: egen yjoq_del=total(Bq) if localf==0
by year county Code: egen yjoq=mean(yjoq_del)
drop *_del
sort year county
by year county: egen yQ=total(Bq)

**current trash distribution
local varl "white black asian hispanic"
foreach v in `varl' {
  gen DBQ`v'=D_B`v'/D_Bpop*yjaq // /yaq
  gen DBO`v'=D_B`v'/D_Bpop*yjoq // /yoq
  sort year county
  by year county: egen IDBQ`v'_del=total(DBQ`v') if yjdup<=1
  by year county: egen IDBQ`v'=mean(IDBQ`v'_del)
  by year county: egen IDBO`v'_del=total(DBO`v') if yjdup<=1
  by year county: egen IDBO`v'=mean(IDBO`v'_del)
  gen PDBQ`v'=IDBQ`v'/yQ*100
  gen PDBO`v'=IDBO`v'/yQ*100  
}
drop *_del DBQ* DBO*

** new trash distribution
sort year county Code
by year county Code: egen yjaNq=total(Cq) // receiving q at year-county level
by year county Code: egen yjoNq_del=total(Cq) if localf==0
by year county Code: egen yjoNq=mean(yjoNq_del)
drop *_del
sort year county
by year county: egen yNQ=total(Cq)

by year county: egen NqTo0pop_del=total(yjaNq) if yjdup<=1 & D_Bpop==0
by year county: egen NqTo0pop=mean(NqTo0pop_del)
replace NqTo0pop=0 if NqTo0pop==.
gen NqTo0poppc=NqTo0pop/yNQ*100

local varl "white black asian hispanic"
foreach v in `varl' {
	*gen D_B`v'p=D_B`v'/D_Bpop*100
  gen DBNQ`v'=D_B`v'/D_Bpop*yjaNq // /yaNq
  gen DBNO`v'=D_B`v'/D_Bpop*yjoNq // /yoNq
  sort year county
  by year county: egen IDBNQ`v'_del=total(DBNQ`v') if yjdup<=1
  by year county: egen IDBNQ`v'=mean(IDBNQ`v'_del)
  by year county: egen IDBNO`v'_del=total(DBNO`v') if yjdup<=1
  by year county: egen IDBNO`v'=mean(IDBNO`v'_del)
  gen PDBNQ`v'=IDBNQ`v'/yNQ*100
  gen PDBNO`v'=IDBNO`v'/yNQ*100
}
drop *_del

local varl "white black asian hispanic"
foreach v in `varl' {
	gen DPDBQ`v'=PDBNQ`v'-PDBQ`v'
	gen DPDBO`v'=PDBNO`v'-PDBO`v'
}
	
************************* TABLE D3 or D8 IN APPENDIX  *******************
********* EJ perspective of NIMBY*********************************************
di "This is the result of counterfactual (with price adjustment) of scenario:"
di "`s' at county `c'"
*see % of trash originating from a county to affected communities
gsort cdis county
di "PANEL B IN TABLE D3 IN APPENDIX D. Distribution of waste after import ban"
di "Or table D8, if this is about Solano County"
list county qto`c' ADqto`c' ADcdolptc ADcmilptc ADexpq ADimpq ADctotCSpt ///
	PDBQwhite PDBQblack PDBQasian PDBQhispanic ///
	DPDBQwhite DPDBQblack DPDBQasian DPDBQhispanic  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 

} // ending "`s'"=="importBan"

******************************************************************************
************ OTHER COUNTERFACTUAL POLICIES ********************************
******************************************************************************
if "`s'"!="importBan" {
**** Calculate baseline level *********
****1. IMPORT DATA OF COUNTERFACTUAL SCENARIO ***********
import excel "$mydir\matlab\output\nimby_counterfactual_`s'_with_eqprice_`c'.xlsx", sheet("Sheet1") firstrow clear
keep year quarter county Code Cpr Cq CCS Crealprice
sort year quarter county Code
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear 
sort year quarter county Code
merge 1:1 year quarter county Code using `tmp', force
drop _merge
erase `tmp'
**Adjust and rename realprice, dridis, diesel price depending on counterfactual************
gen Nrealprice=Crealprice
if "`s'"=="importTax" {
replace Nrealprice=Nrealprice*1.10  if localf==0 & desco=="`c'"
}
if "`s'"=="trashTax" {
replace Nrealprice=Nrealprice*1.10
}
*mile tax affects share and flows, not directly on price or distance
gen Ndridis=dridis

bys year quarter county: egen CCS_del=mean(CCS)
drop CCS
rename CCS_del CCS

gen DEL_q=Cq-Bq
gen Nq=q+DEL_q/Bq*q
replace Nq=0 if DEL_q<=1e-2 & q==0
replace Nq=Cq-Bq if DEL_q>1e-2 & q==0
replace Nq=0 if localf==0
gen Dq=Nq-q

******************ECONOMIC VARIABLES *********************
keep if year==2010
bys year county: egen havelocalc=total(localf)
replace havelocalc=1 if havelocalc>0 & havelocalc<.
******************** CURRENT STATISTICS. ACTUAL LEVEL *****************
**calculate export amount, import amount, and net import by county
sort year county  // exports
by year county: egen Atcq=total(q)
*export to other county, but within California
by year county: egen Aexpq_del= total(q) if localf==0 & j~="oos" 
by year county: egen Aexpq= mean(Aexpq_del)
replace Aexpq=0 if Aexpq==.
sort year desco // imports
*import by destination county
by year desco: egen Aimpdes_del =total(q) if localf==0 & j~="oos" 
by year desco: egen Aimpdes=mean(Aimpdes_del) // spread values to des cnty level
replace Aimpdes=0 if Aimpdes==.
gen Aimpq_del= Aimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimpq=mean(Aimpq_del) // spread values to county
replace Aimpq=0 if Aimpq==.
drop *_del Aimpdes
gen Anetim= Aimpq - Aexpq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen Arqdes_del=total(q)
gen Arq_del=Arqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Arq=mean(Arq_del)
drop *_del

**payment, total tipping fees
sort year county // exports
gen Adol_del=q*realprice
by year county: egen Acdol=total(Adol_del) // total tipping fees
replace Acdol=0 if Acdol==.
by year county: egen Aexdol_c_del=total(Adol_del) if localf==0 & j~="oos"
by year county: egen Aexdol=mean(Aexdol_c_del)
replace Aexdol=0 if Aexdol==.
sort year desco // imports
by year desco: egen Aimdol_d_del=total(Adol_del) if localf==0 & j~="oos"
by year desco: egen Aimdold=mean(Aimdol_d_del)
replace Aimdold=0 if Aimdold==.
gen Aimdol_c_del=Aimdold if county==desco // spread values from des cnty to cnty
sort year county
by year county: egen Aimdol=mean(Aimdol_c_del)
replace Aimdol=0 if Aimdol==.
gen Anetimdol=Aimdol-Aexdol
drop *_del
gen Acdolpt=Acdol/Atcq // dollar per ton

**trash miles
gen Acmil_del=q*dridis
sort year county
by year county: egen Acmil=total(Acmil_del) // total trash ton-miles
replace Acmil=0 if Acmil==.
drop *_del
gen Acmilpt=Acmil/Atcq // dollar per ton

******************* SIMULATED/MODEL IMPLIED STATISTICS ****************
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen tcq=total(Bq)
by year county: egen gq_del=total(Bq) if j~="oos"
by year county: egen gq=mean(gq_del)
*export to other county, but within California
by year county: egen expq_del= total(Bq) if localf==0 & j~="oos"
by year county: egen expq= mean(expq_del)
replace expq=0 if expq==.
sort year desco //imports
*import by destination county
by year desco: egen impdes_del =total(Bq) if localf==0 & j~="oos" 
by year desco: egen impdes=mean(impdes_del) // spread values to des cnty level
replace impdes=0 if impdes==.
gen impq_del= impdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen impq=mean(impq_del) // spread values to county
replace impq=0 if impq==.
drop *_del impdes
gen netim= impq - expq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen rqdes_del=total(Bq)
gen rq_del=rqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen rq=mean(rq_del)
replace rq=0 if rq==.
drop *_del

**payment, total tipping fees
sort year county //exports
gen dol_del=Bq*realprice
by year county: egen cdol=total(dol_del) // total tipping fees
replace cdol=0 if cdol==.
by year county: egen exdol_c_del=total(dol_del) if localf==0 & j~="oos"
by year county: egen exdol=mean(exdol_c_del)
replace exdol=0 if exdol==.
sort year desco //imports
by year desco: egen imdol_d_del=total(dol_del) if localf==0 & j~="oos"
by year desco: egen imdold=mean(imdol_d_del)
replace imdold=0 if imdold==.
gen imdol_c_del=imdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen imdol=mean(imdol_c_del)
replace imdol=0 if imdol==.
gen netimdol=imdol-exdol
gen cdolpt=cdol/tcq // dollar per ton
drop *_del
gen exdolpt=exdol/expq // dollar per ton for exports

*trash miles
gen cmil_del=Bq*dridis
sort year county
by year county: egen cmil=total(cmil_del) // total trash ton-miles
replace cmil=0 if cmil==.  // trash mileage
gen cmilpt=cmil/tcq // dollar per ton

by year county: egen exmil_c_del=total(cmil_del) if localf==0 & j~="oos"
by year county: egen exmil=mean(exmil_c_del)
replace exmil=0 if exmil==.
drop *_del
gen exmilpt=exmil/expq // mile per trip for exports

********************NEW LEVELS *******
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen Ntcq=total(Cq)
by year county: egen Ngq_del=total(Cq) if j~="oos"
by year county: egen Ngq=mean(Ngq_del)
by year county: egen Nexpq_del= total(Cq) if localf==0 & j~="oos"
by year county: egen Nexpq= mean(Nexpq_del)
replace Nexpq=0 if Nexpq==.
sort year desco //imports
by year desco: egen Nimpdes_del =total(Cq) if localf==0 & j~="oos" // calculate import by des county
by year desco: egen Nimpdes=mean(Nimpdes_del) // spread values to des cnty level
replace Nimpdes=0 if Nimpdes==.
gen Nimpq_del= Nimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Nimpq=mean(Nimpq_del) // spread values to county
replace Nimpq=0 if Nimpq==.
drop *_del Nimpdes
gen Nnetim= Nimpq - Nexpq

**total receiving trash
sort year desco
by year desco: egen Nrqdes_del=total(Cq)
gen Nrq_del=Nrqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Nrq=mean(Nrq_del)
replace Nrq=0 if Nrq==.
drop *_del

*payment
sort year county //exports
gen Ndol_del=Cq*Nrealprice
by year county: egen Ncdol=total(Ndol_del)
replace Ncdol=0 if Ncdol==.
by year county: egen Nexdol_c_del=total(Ndol_del) if localf==0 & j~="oos"
by year county: egen Nexdol=mean(Nexdol_c_del)
replace Nexdol=0 if Nexdol==.
sort year desco //imports
by year desco: egen Nimdol_d_del=total(Ndol_del) if localf==0 & j~="oos"
by year desco: egen Nimdold=mean(Nimdol_d_del)
replace Nimdold=0 if Nimdold==.
gen Nimdol_c_del=Nimdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen Nimdol=mean(Nimdol_c_del)
replace Nimdol=0 if Nimdol==.
gen Nnetimdol=Nimdol-Nexdol
gen Ncdolpt=Ncdol/Ntcq // dollar per ton
drop *_del
gen Nexdolpt=Nexdol/Nexpq // dollar per ton for exports

*trash miles
gen Ncmil_del=Cq*Ndridis
sort year county
by year county: egen Ncmil=total(Ncmil_del)
replace Ncmil=0 if Ncmil==.
gen Ncmilpt=Ncmil/Ntcq // mile per trip
by year county: egen Nexmil_c_del=total(Ncmil_del) if localf==0 & j~="oos"
by year county: egen Nexmil=mean(Nexmil_c_del)
replace Nexmil=0 if Nexmil==.
drop *_del
gen Nexmilpt=Nexmil/Nexpq // mile per trip for exports

sort year quarter county
by year quarter county: egen cntybq=total(Bq)
by year quarter county: egen cntycq=total(Cq)
gen totCS=CS*cntybq
gen NtotCS=CCS*cntycq
sort year county quarter
by year county quarter: gen dup=cond(_N==1,0,_n)
by year county: egen ctotCS_del=total(totCS) if dup<=1
by year county: egen ctotCS=mean(ctotCS_del)
by year county: egen NctotCS_del=total(NtotCS) if  dup<=1
by year county: egen NctotCS=mean(NctotCS_del) 
gen ctotCSpt=ctotCS/tcq // dollar per ton
gen NctotCSpt=NctotCS/Ntcq // dollar per ton
drop *_del
gen expqp = expq*100/tcq // percentage of export
*gen Nexpqp= Nexpqp*100/tcq
gen impqp = impq*100/tcq
******** change in external cost of transportation ******
*Austin2018 estimates external cost of truck trans=2.62-5.86 in 2014 cents per ton-mile
*This means 1.91-4.26 in 2000 cents per ton-mile
gen extran1=cmil*1.91/100 // in dollar
gen Nextran1=Ncmil*1.91/100 // in dollar

*total waste to county of policy
bys year county: egen qto`c'_del=total(Bq) if desco=="`c'"
bys year county: egen qto`c'=mean(qto`c'_del)
replace qto`c'=0 if qto`c'==.
bys year county: egen Nqto`c'_del=total(Cq) if desco=="`c'"
bys year county: egen Nqto`c'=mean(Nqto`c'_del)
replace Nqto`c'=0 if Nqto`c'==. 
drop *_del
replace Nqto`c'=. if qto`c'==0 & Nqto`c'==0
replace qto`c'=. if qto`c'==0 & Nqto`c'==.

local varl "rq expq impq cdol cmil ctotCS extran1 qto`c'"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}

by year: egen atcq=mean(tcq) if dupc<=1 &  havelocalc==1 & county~="`c'"

local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'pt=AD`v'/atcq
}

replace ADextran1=0 if abs(ADcmilpt)<0.0005
replace ADcmilpt=0 if abs(ADcmilpt)<0.0005

**change by county ******
local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'ptc=AD`v'/tcq
}

*******###############################################################********
***benefits of reduction in export to break even with the hauling cost ***
*each ycounty has importq impq and cost ctotCS
gen hhvaluec=-ADctotCS/ADimpq
	
******* change in price at facility level ***************
egen tagfq=tag(year quarter Code)
bys year Code: egen lanpri_del=mean(realprice) if tagfq==1
bys year Code: egen lanpri=mean(lanpri_del)
bys year Code: egen Clanpri_del=mean(Crealprice) if tagfq==1
bys year Code: egen Clanpri=mean(Clanpri_del)
bys year Code: egen Nlanpri_del=mean(Nrealprice) if tagfq==1
bys year Code: egen Nlanpri=mean(Nlanpri_del)
drop *_del

** change in price at facilities at the county (desco)
bys year desco: egen avgpricec_del=mean(lanpri)
gen avgpricec_del2=avgpricec_del if county==desco
bys year county: egen avgpricec=mean(avgpricec_del2)
drop *_del*
bys year desco: egen Navgpricec_del=mean(Clanpri) 
gen Navgpricec_del2=Navgpricec_del if county==desco
bys year county: egen Navgpricec=mean(Navgpricec_del2)
drop *_del*


*tag facility in counties that satisfy conditions
egen tagf=tag(year Code) 

by year: egen avgprice=mean(lanpri) if tagf==1
by year: egen Navgprice=mean(Clanpri) if tagf==1

bys year Code: egen lanq=total(Bq) // total waste at a facility
by year: egen avglanq=mean(lanq) if tagf==1
bys year Code: egen Nlanq=total(Cq)
by year: egen Navglanq=mean(Nlanq) if tagf==1

*There may be difference change bw facility that received foreign flow vs local
*tag facility that received intercounty flow before policy.
egen tagff=tag(year Code) if localf==0 & q>0
bys year Code: egen havefq=total(tagff)

by year: egen avgpriceother_del=mean(lanpri) if tagf==1 & desco~="`c'"
by year: egen avgpriceother=mean(avgpriceother_del)
by year: egen Cavgpriceother_del=mean(Clanpri) if tagf==1 & desco~="`c'"
by year: egen Cavgpriceother=mean(Cavgpriceother_del)
by year: egen Navgpriceother_del=mean(Nlanpri) if tagf==1 & desco~="`c'"
by year: egen Navgpriceother=mean(Navgpriceother_del)
gen ADCavgpriceother=Cavgpriceother - avgpriceother
gen ADNavgpriceother=Navgpriceother - avgpriceother // including tax, if any

by year: egen avgpricef_del=mean(lanpri) if havefq==1 & tagf==1 & desco~="`c'"
by year: egen avgpricef=mean(avgpricef_del)
by year: egen Navgpricef_del=mean(Clanpri) if havefq==1 & tagf==1 & desco~="`c'"
by year: egen Navgpricef=mean(Navgpricef_del)
by year: egen avgpricel_del=mean(lanpri) if havefq==0 & tagf==1 & desco~="`c'"
by year: egen avgpricel=mean(avgpricel_del)
by year: egen Navgpricel_del=mean(Clanpri) if havefq==0 & tagf==1 & desco~="`c'"
by year: egen Navgpricel=mean(Navgpricel_del)

by year: egen avgpricef`c'_del=mean(lanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen avgpricef`c'=mean(avgpricef`c'_del)
by year: egen Navgpricef`c'_del=mean(Clanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen Navgpricef`c'=mean(Navgpricef`c'_del)
by year: egen avgpricel`c'_del=mean(lanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen avgpricel`c'=mean(avgpricel`c'_del)
by year: egen Navgpricel`c'_del=mean(Clanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen Navgpricel`c'=mean(Navgpricel`c'_del)
*price including tax, if any
by year: egen Ntavgpricef`c'_del=mean(Nlanpri) if havefq==1 & tagf==1 & desco=="`c'"
by year: egen Ntavgpricef`c'=mean(Ntavgpricef`c'_del)
by year: egen Ntavgpricel`c'_del=mean(Nlanpri) if havefq==0 & tagf==1 & desco=="`c'"
by year: egen Ntavgpricel`c'=mean(Ntavgpricel`c'_del)
gen ADNavgpricef`c' = Ntavgpricef`c' - avgpricef`c' // including tax, if any
gen ADNavgpricel`c' = Ntavgpricel`c' - avgpricel`c' // including tax, if any

drop *_del

by year: egen avglanqf_del=mean(lanq) if havefq==1 & tagf==1
by year: egen avglanqf=mean(avglanqf_del)
by year: egen Navglanqf_del=mean(Nlanq) if havefq==1 & tagf==1
by year: egen Navglanqf=mean(Navglanqf_del)
by year: egen avglanql_del=mean(lanq) if havefq==0 & tagf==1
by year: egen avglanql=mean(avglanql_del)
by year: egen Navglanql_del=mean(Nlanq) if havefq==0 & tagf==1
by year: egen Navglanql=mean(Navglanql_del)

drop *_del

**Export to Marin
bys year: egen exto`c'_del=total(Bq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen exto`c'=mean(exto`c'_del) 
bys year: egen Nexto`c'_del=total(Cq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen Nexto`c'=mean(Nexto`c'_del)

**Total intercounty in CA
bys year: egen interq_del=total(Bq) if localf==0 & j~="oos"
bys year: egen interq=mean(interq_del)
bys year: egen Ninterq_del=total(Cq) if localf==0 & j~="oos"
bys year: egen Ninterq=mean(Ninterq_del)
drop *_del

local varl "exto`c' interq avgpricec avgprice avgpricef avgpricel avgpricef`c' avgpricel`c' avglanq avglanqf avglanql"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}
	

*******###############################################################********
* Notice ADctotCS shows change in cost
*******************************************************************************
di "This is the result of counterfactual (with price adjustment) of scenario:"
di "`s' in county `c'"

label var exto`c' "import in `c' (baseline)"
label var ADexto`c' "change in import in `c'"
label var interq "intercounty waste in CA"
label var ADinterq "change in intercounty waste in CA"
label var expqp "percentage of export (baseline)"
label var expq "export amount (baseline)"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount (baseline)"
label var ADimpq "change in import amount"
label var cdolpt "tipping fees (baseline)"
label var ADcdolptc "change in tipping fees"
label var cmilpt "mileage (baseline)"
label var ADcmilptc "change in mileage"
label var ADctotCSptc "change in discarding cost"
label var extran1 "external transport cost (baseline)"
label var ADextran1 "change in external transport cost"
label var hhvaluec "household value"
label var expqp "percentage of export (baseline)"
label var expq "export amount"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount"
label var ADimpq "change in import amount"

*******************  TABLE 3. Column (2): IMPORT TAX ************************
di "TABLE 3. Column (2): IMPORT TAX"
di "Or table D5, if this is about Solano County"
di "PANEL B: CHANGE IN THE COUNTY THAT IMPLEMENTS NIMBY POLICIES"
sum exto`c' ADexto`c' interq ADinterq expqp expq ADexpq impqp impq ADimpq ///
	cdolpt ADcdolptc cmilpt ADcmilptc ADctotCSptc ///
	extran1 ADextran1 hhvaluec ///
	if year==2010  & dupc<=1 & havelocalc==1 & county=="`c'"
di "PANEL C: CHANGE IN THE OTHER COUNTIES"
sum expqp expq ADexpq impqp impq ADimpq  ///
	cdolpt ADcdolptc cmilpt ADcmilptc ADctotCSptc ///
	extran1 ADextran1 hhvaluec ///
	if year==2010  & dupc<=1 & havelocalc==1 & county~="`c'"	
di "PANEL A: CHANGE AT FACILITY LEVEL"	
sum avgprice ADavgprice ADavgpricep ///
	avgpricef`c' ADavgpricef`c' ADavgpricef`c'p ///
	avgpricel`c' ADavgpricel`c' ADavgpricel`c'p ///
	avgpricef ADavgpricef ADavgpricefp ///
	avgpricel ADavgpricel ADavgpricelp  ///
	ADNavgpricef`c' ADNavgpricel`c' ///
	avgpriceother ADCavgpriceother ADNavgpriceother if year==2010 & tagf==1


***********  TABLE D2 or D7 IN APPENDIX  ************************************
gen cpwlat`c'_del=cpwlat if county=="`c'"
egen cpwlat`c'=mean(cpwlat`c'_del)
gen cpwlon`c'_del=cpwlon if county=="`c'"
egen cpwlon`c'=mean(cpwlon`c'_del)
drop *_del

geodist cpwlat cpwlon cpwlat`c' cpwlon`c', miles gen(cdis)

di "TABLE D2 IN APPENDIX D: PANEL B: IMPORT TAX IN EQUILIBRIUM MODEL"
di "Or table D7, if this is about Solano County"
gsort cdis county
list county gq rq ADrq ADexpq ADimpq ///
	ADextran1 ADcdolptc ADcmilptc ADctotCSptc hhvaluec cdis  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 
	
list county avgpricec ADavgpricec ADavgpricecp ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 

** DISTRIBUTIONAL EFFECTS. APPENDIX **********************************
keep if year==2010

*** percent of trash of origin county to its all within-60miles facilities
sort year county Code
by year county Code: gen yjdup=cond(_N==1,0,_n)
by year county Code: egen yjaq=total(Bq) // receiving q at year-county level
by year county Code: egen yjoq_del=total(Bq) if localf==0
by year county Code: egen yjoq=mean(yjoq_del)
drop *_del
sort year county
by year county: egen yQ=total(Bq)

local varl "white black asian hispanic"
foreach v in `varl' {
  gen DBQ`v'=D_B`v'/D_Bpop*yjaq // /yaq
  gen DBO`v'=D_B`v'/D_Bpop*yjoq // /yoq
  sort year county
  by year county: egen IDBQ`v'_del=total(DBQ`v') if yjdup<=1
  by year county: egen IDBQ`v'=mean(IDBQ`v'_del)
  by year county: egen IDBO`v'_del=total(DBO`v') if yjdup<=1
  by year county: egen IDBO`v'=mean(IDBO`v'_del)
  gen PDBQ`v'=IDBQ`v'/yQ*100
  gen PDBO`v'=IDBO`v'/yQ*100  
}
drop *_del DBQ* DBO*

** new trash distribution
sort year county Code
by year county Code: egen yjaNq=total(Cq) // receiving q at year-county level
by year county Code: egen yjoNq_del=total(Cq) if localf==0
by year county Code: egen yjoNq=mean(yjoNq_del)
drop *_del
sort year county
by year county: egen yNQ=total(Cq)

by year county: egen NqTo0pop_del=total(yjaNq) if yjdup<=1 & D_Bpop==0
by year county: egen NqTo0pop=mean(NqTo0pop_del)
replace NqTo0pop=0 if NqTo0pop==.
gen NqTo0poppc=NqTo0pop/yNQ*100

local varl "white black asian hispanic"
foreach v in `varl' {
	*gen D_B`v'p=D_B`v'/D_Bpop*100
  gen DBNQ`v'=D_B`v'/D_Bpop*yjaNq // /yaNq
  gen DBNO`v'=D_B`v'/D_Bpop*yjoNq // /yoNq
  sort year county
  by year county: egen IDBNQ`v'_del=total(DBNQ`v') if yjdup<=1
  by year county: egen IDBNQ`v'=mean(IDBNQ`v'_del)
  by year county: egen IDBNO`v'_del=total(DBNO`v') if yjdup<=1
  by year county: egen IDBNO`v'=mean(IDBNO`v'_del)
  gen PDBNQ`v'=IDBNQ`v'/yNQ*100
  gen PDBNO`v'=IDBNO`v'/yNQ*100
}
drop *_del

local varl "white black asian hispanic"
foreach v in `varl' {
	gen DPDBQ`v'=PDBNQ`v'-PDBQ`v'
	gen DPDBO`v'=PDBNO`v'-PDBO`v'
}

label var PDBQwhite "% of waste going to white communities (baseline)"
label var PDBQblack "% of waste going to black communities (baseline)"
label var PDBQasian "% of waste going to Asian communities (baseline)"
label var PDBQhispanic "% of waste going to Hispanic communities (baseline)"
label var DPDBQwhite "change in % of waste going to white communities"
label var DPDBQblack "change in % of waste going to black communities"
label var DPDBQasian "change in % of waste going to Asian communities"
label var DPDBQhispanic "change in % of waste going to Hispanic communities"

*********************** TABLE D4 or D9 IN APPENDIX D *******************
********* EJ perspective of NIMBY*********************************************
di "This is the result of counterfactual (with price adjustment) of scenario:"
di "`s' at county `c'"
di "PANEL B IN TABLE D4 IN APPENDIX D. Distribution of waste after import tax"
di "Or table D9, if this is about Solano County"
*see % of trash originating from a county to affected communities
gsort cdis county
list county qto`c' ADqto`c' ADcdolptc ADcmilptc ADexpq ADimpq ADctotCSpt ///
	PDBQwhite PDBQblack PDBQasian PDBQhispanic ///
	DPDBQwhite DPDBQblack DPDBQasian DPDBQhispanic  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.)) 
	
 } // ending "`s'"!="importBan"
  
} // ending foreach s in `scenario'
} // ending foreach c in `cnty'

******************************************************************
**** RESULTS OF POLICY COUNTERFACTUALS IN FIXED-PRICE MODEL ***********
**********************************************************************
*** TABLES IN APPENDIX D ******************************

foreach c in `cnty' {
foreach s in `scenario' {
	if "`s'"=="importBan" {
****1. IMPORT DATA OF COUNTERFACTUAL SCENARIO ***********
import excel "$mydir\matlab\output\nimby_counterfactual_`s'_`c'.xlsx", sheet("Sheet1") firstrow clear
keep year quarter county Code Cpr Cq CCS 
sort year quarter county Code
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear 
*replace TC=99999 if localf==0 & county=="`c'"
sort year county
by year county: egen slocal=total(localf)
replace slocal=1 if slocal>0 & slocal<. // whether a county has a local facility
rename slocal havef

bys yq county: egen slocal=total(localf)
replace slocal=1 if slocal>0 & slocal<.
*drop if slocal==0 & havef==1
********************back to original code below ******
sort year quarter county Code
merge 1:1 year quarter county Code using `tmp', force
drop _merge
erase `tmp'
**Adjust and rename realprice, dridis, diesel price depending on counterfactual************
gen Nrealprice=realprice
// PRICE HAULERS PAY, POST TAX. Crealprice: eq price
replace Nrealprice=Nrealprice 
gen Ndridis=dridis
bys year quarter county: egen CCS_del=mean(CCS)
drop CCS
rename CCS_del CCS

gen DEL_q=Cq-Bq
gen Nq=q+DEL_q/Bq*q
replace Nq=0 if DEL_q<=1e-2 & q==0
replace Nq=Cq-Bq if DEL_q>1e-2 & q==0
replace Nq=0 if localf==0
gen Dq=Nq-q

******************ECONOMIC VARIABLES *********************
*** Calculate policy variables at current state (with actual q)
keep if year==2010
bys year county: egen havelocalc=total(localf)
replace havelocalc=1 if havelocalc>0 & havelocalc<.
******************** CURRENT STATISTICS. ACTUAL LEVEL *****************
**calculate export amount, import amount, and net import by county
sort year county  // exports
by year county: egen Atcq=total(q)
*export to other county, but within California
by year county: egen Aexpq_del= total(q) if localf==0 & j~="oos" 
by year county: egen Aexpq= mean(Aexpq_del)
replace Aexpq=0 if Aexpq==.
sort year desco // imports
*import by destination county
by year desco: egen Aimpdes_del =total(q) if localf==0 & j~="oos" 
by year desco: egen Aimpdes=mean(Aimpdes_del) // spread values to des cnty level
replace Aimpdes=0 if Aimpdes==.
gen Aimpq_del= Aimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimpq=mean(Aimpq_del) // spread values to county
replace Aimpq=0 if Aimpq==.
drop *_del Aimpdes
gen Anetim= Aimpq - Aexpq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen Arqdes_del=total(q)
gen Arq_del=Arqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Arq=mean(Arq_del)
drop *_del

**payment, total tipping fees
sort year county // exports
gen Adol_del=q*realprice
by year county: egen Acdol=total(Adol_del) // total tipping fees
replace Acdol=0 if Acdol==.
by year county: egen Aexdol_c_del=total(Adol_del) if localf==0 & j~="oos"
by year county: egen Aexdol=mean(Aexdol_c_del)
replace Aexdol=0 if Aexdol==.
sort year desco // imports
by year desco: egen Aimdol_d_del=total(Adol_del) if localf==0 & j~="oos"
by year desco: egen Aimdold=mean(Aimdol_d_del)
replace Aimdold=0 if Aimdold==.
gen Aimdol_c_del=Aimdold if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimdol=mean(Aimdol_c_del)
replace Aimdol=0 if Aimdol==.
gen Anetimdol=Aimdol-Aexdol
drop *_del
gen Acdolpt=Acdol/Atcq // dollar per ton

**trash miles
gen Acmil_del=q*dridis
sort year county
by year county: egen Acmil=total(Acmil_del) // total trash ton-miles
replace Acmil=0 if Acmil==.
drop *_del
gen Acmilpt=Acmil/Atcq // mile per ton

******************* SIMULATED/MODEL IMPLIED STATISTICS ****************
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen tcq=total(Bq)
by year county: egen gq_del=total(Bq) if j~="oos"
by year county: egen gq=mean(gq_del)
*export to other county, but within California
by year county: egen expq_del= total(Bq) if localf==0 & j~="oos"
by year county: egen expq= mean(expq_del)
replace expq=0 if expq==.
sort year desco //imports
*import by destination county
by year desco: egen impdes_del =total(Bq) if localf==0 & j~="oos" 
by year desco: egen impdes=mean(impdes_del) // spread values to des cnty level
replace impdes=0 if impdes==.
gen impq_del= impdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen impq=mean(impq_del) // spread values to county
replace impq=0 if impq==.
drop *_del impdes
gen netim= impq - expq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen rqdes_del=total(Bq)
gen rq_del=rqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen rq=mean(rq_del)
replace rq=0 if rq==.
drop *_del

**payment, total tipping fees
sort year county //exports
gen dol_del=Bq*realprice
by year county: egen cdol=total(dol_del) // total tipping fees
replace cdol=0 if cdol==.
by year county: egen exdol_c_del=total(dol_del) if localf==0 & j~="oos"
by year county: egen exdol=mean(exdol_c_del)
replace exdol=0 if exdol==.
sort year desco //imports
by year desco: egen imdol_d_del=total(dol_del) if localf==0 & j~="oos"
by year desco: egen imdold=mean(imdol_d_del)
replace imdold=0 if imdold==.
gen imdol_c_del=imdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen imdol=mean(imdol_c_del)
replace imdol=0 if imdol==.
gen netimdol=imdol-exdol
drop*_del
gen cdolpt=cdol/tcq // dollar per ton
gen exdolpt=exdol/expq // dollar per ton for exports

*trash miles
gen cmil_del=Bq*dridis
sort year county
by year county: egen cmil=total(cmil_del) // total trash ton-miles
replace cmil=0 if cmil==.  // trash mileage
by year county: egen exmil_c_del=total(cmil_del) if localf==0 & j~="oos"
by year county: egen exmil=mean(exmil_c_del)
replace exmil=0 if exmil==.
drop *_del
gen cmilpt=cmil/tcq // mile per trip
gen exmilpt=exmil/expq // mile per trip for exports

********************NEW LEVELS *******
**calculate export amounts and import amount using q_tcj
sort year county  
by year county: egen Ntcq=total(Cq)
by year county: egen Ngq_del=total(Cq) if j~="oos"
by year county: egen Ngq=mean(Ngq_del)
by year county: egen Nexpq_del= total(Cq) if localf==0 & j~="oos" // exports
by year county: egen Nexpq= mean(Nexpq_del)
replace Nexpq=0 if Nexpq==.
sort year desco //imports
by year desco: egen Nimpdes_del =total(Cq) if localf==0 & j~="oos" // calculate import by des county
by year desco: egen Nimpdes=mean(Nimpdes_del) // spread values to des cnty level
replace Nimpdes=0 if Nimpdes==.
gen Nimpq_del= Nimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Nimpq=mean(Nimpq_del) // spread values to county
replace Nimpq=0 if Nimpq==.
drop *_del Nimpdes
gen Nnetim= Nimpq - Nexpq

**total receiving trash
sort year desco
by year desco: egen Nrqdes_del=total(Cq)
gen Nrq_del=Nrqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Nrq=mean(Nrq_del)
replace Nrq=0 if Nrq==.
drop *_del

*payment
sort year county //exports
gen Ndol_del=Cq*Nrealprice
by year county: egen Ncdol=total(Ndol_del)
replace Ncdol=0 if Ncdol==.
by year county: egen Nexdol_c_del=total(Ndol_del) if localf==0 & j~="oos"
by year county: egen Nexdol=mean(Nexdol_c_del)
replace Nexdol=0 if Nexdol==.
sort year desco //imports
by year desco: egen Nimdol_d_del=total(Ndol_del) if localf==0 & j~="oos"
by year desco: egen Nimdold=mean(Nimdol_d_del)
replace Nimdold=0 if Nimdold==.
gen Nimdol_c_del=Nimdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen Nimdol=mean(Nimdol_c_del)
replace Nimdol=0 if Nimdol==.
gen Nnetimdol=Nimdol-Nexdol
gen Ncdolpt=Ncdol/Ntcq // dollar per ton
drop *_del
gen Nexdolpt=Nexdol/Nexpq // dollar per ton for exports

*trash miles
gen Ncmil_del=Cq*Ndridis
sort year county
by year county: egen Ncmil=total(Ncmil_del)
replace Ncmil=0 if Ncmil==.
gen Ncmilpt=Ncmil/tcq // mile per trip
by year county: egen Nexmil_c_del=total(Ncmil_del) if localf==0 & j~="oos"
by year county: egen Nexmil=mean(Nexmil_c_del)
replace Nexmil=0 if Nexmil==.
drop *_del
gen Nexmilpt=Nexmil/Nexpq // mile per trip for exports

sort year quarter county
by year quarter county: egen cntybq=total(Bq)
by year quarter county: egen cntycq=total(Cq)
gen totCS=CS*cntybq
gen NtotCS=CCS*cntycq
*gen ADtotCS=NtotCS-totCS
sort year county quarter
by year county quarter: gen dup=cond(_N==1,0,_n)
by year county: egen ctotCS_del=total(totCS) if dup<=1
by year county: egen ctotCS=mean(ctotCS_del)
by year county: egen NctotCS_del=total(NtotCS) if  dup<=1
by year county: egen NctotCS=mean(NctotCS_del) 
gen ctotCSpt=ctotCS/tcq // dollar per ton
gen NctotCSpt=NctotCS/Ntcq // dollar per ton
drop *_del
gen expqp = expq*100/tcq // percentage of export
*gen Nexpqp= Nexpqp*100/tcq
gen impqp = impq*100/tcq
******** change in external cost of transportation ******
*Austin2018 estimates external cost of truck trans=2.62-5.86 in 2014 cents per ton-mile
*This means 1.91-4.26 in 2000 cents per ton-mile
gen extran1=cmil*1.91/100 // in dollar
gen Nextran1=Ncmil*1.91/100 // in dollar
*ADextran <0: reduce external trans cost

*total waste to county of policy
bys year county: egen qto`c'_del=total(Bq) if desco=="`c'"
bys year county: egen qto`c'=mean(qto`c'_del)
replace qto`c'=0 if qto`c'==.
bys year county: egen Nqto`c'_del=total(Cq) if desco=="`c'"
bys year county: egen Nqto`c'=mean(Nqto`c'_del)
replace Nqto`c'=0 if Nqto`c'==. 
drop *_del
replace Nqto`c'=. if qto`c'==0 & Nqto`c'==0
replace qto`c'=. if qto`c'==0 & Nqto`c'==.

**Export to Marin
bys year: egen exto`c'_del=total(Bq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen exto`c'=mean(exto`c'_del) 
bys year: egen Nexto`c'_del=total(Cq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen Nexto`c'=mean(Nexto`c'_del)

**Total intercounty in CA
bys year: egen interq_del=total(Bq) if localf==0 & j~="oos"
bys year: egen interq=mean(interq_del)
bys year: egen Ninterq_del=total(Cq) if localf==0 & j~="oos"
bys year: egen Ninterq=mean(Ninterq_del)
drop *_del


local varl "rq expq impq cdol cmil ctotCS extran1 qto`c' exto`c' interq"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}

******* change at average level***************
by year: egen atcq=mean(tcq) if dupc<=1 & havelocalc==1 & county~="`c'"

local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'pt=AD`v'/atcq
}

replace ADextran1=0 if abs(ADcmilpt)<0.0005
replace ADcmilpt=0 if abs(ADcmilpt)<0.0005

**change by county ******
local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'ptc=AD`v'/tcq
}

***benefits of reduction in export to break even with the hauling cost ***
*each ycounty has importq impq and cost ctotCS
gen hhvaluec=-ADctotCS/ADimpq
	
*******************************************************************************
di "This is the result of counterfactual (without price adjustment) of scenario:"
di "`s' in county `c'"

label var exto`c' "import in `c' (baseline)"
label var ADexto`c' "change in import in `c'"
label var interq "intercounty waste in CA"
label var ADinterq "change in intercounty waste in CA"
label var expqp "percentage of export (baseline)"
label var expq "export amount (baseline)"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount (baseline)"
label var ADimpq "change in import amount"
label var cdolpt "tipping fees (baseline)"
label var ADcdolptc "change in tipping fees"
label var cmilpt "mileage (baseline)"
label var ADcmilptc "change in mileage"
label var ADctotCSptc "change in discarding cost"
label var extran1 "external transport cost (baseline)"
label var ADextran1 "change in external transport cost"
label var hhvaluec "household value"
label var expqp "percentage of export (baseline)"
label var expq "export amount"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount"
label var ADimpq "change in import amount"

********  TABLE D1 OR D6 IN APPENDIX: PANEL A: IMPORT BAN IN FIXED-PRICE MODEL ****
gen cpwlat`c'_del=cpwlat if county=="`c'"
egen cpwlat`c'=mean(cpwlat`c'_del)
gen cpwlon`c'_del=cpwlon if county=="`c'"
egen cpwlon`c'=mean(cpwlon`c'_del)
drop *_del

***********  APPENDIX  D  ***********************************
geodist cpwlat cpwlon cpwlat`c' cpwlon`c', miles gen(cdis)
di "TABLE D1 IN APPENDIX D: PANEL A. IMPORT BAN IN FIXED-PRICE MODEL"
di "Or table D6, if this is about Solano County"
gsort cdis county
list county gq rq ADrq ADexpq ADimpq ///
	ADextran1 ADcdolptc ADcmilptc ADctotCSptc hhvaluec cdis ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.))

****************************************************************************
*** DISTRIBUTIONAL EFFECTS. APPENDIX D3 OR D8 ******************************
keep if year==2010

*** percent of trash of origin county to its all within-60miles facilities
sort year county Code
by year county Code: gen yjdup=cond(_N==1,0,_n)
by year county Code: egen yjaq=total(Bq) // receiving q at year-county level
by year county Code: egen yjoq_del=total(Bq) if localf==0
by year county Code: egen yjoq=mean(yjoq_del)
drop *_del
sort year county
by year county: egen yQ=total(Bq)
**current trash distribution
local varl "white black asian hispanic"
foreach v in `varl' {
  gen DBQ`v'=D_B`v'/D_Bpop*yjaq // /yaq
  gen DBO`v'=D_B`v'/D_Bpop*yjoq // /yoq
  sort year county
  by year county: egen IDBQ`v'_del=total(DBQ`v') if yjdup<=1
  by year county: egen IDBQ`v'=mean(IDBQ`v'_del)
  by year county: egen IDBO`v'_del=total(DBO`v') if yjdup<=1
  by year county: egen IDBO`v'=mean(IDBO`v'_del)
  gen PDBQ`v'=IDBQ`v'/yQ*100
  gen PDBO`v'=IDBO`v'/yQ*100  
}
drop *_del DBQ* DBO*

** new trash distribution
sort year county Code
by year county Code: egen yjaNq=total(Cq) // receiving q at year-county level
by year county Code: egen yjoNq_del=total(Cq) if localf==0
by year county Code: egen yjoNq=mean(yjoNq_del)
drop *_del
sort year county
by year county: egen yNQ=total(Cq)

by year county: egen NqTo0pop_del=total(yjaNq) if yjdup<=1 & D_Bpop==0
by year county: egen NqTo0pop=mean(NqTo0pop_del)
replace NqTo0pop=0 if NqTo0pop==.
gen NqTo0poppc=NqTo0pop/yNQ*100

local varl "white black asian hispanic"
foreach v in `varl' {
	*gen D_B`v'p=D_B`v'/D_Bpop*100
  gen DBNQ`v'=D_B`v'/D_Bpop*yjaNq // /yaNq
  gen DBNO`v'=D_B`v'/D_Bpop*yjoNq // /yoNq
  sort year county
  by year county: egen IDBNQ`v'_del=total(DBNQ`v') if yjdup<=1
  by year county: egen IDBNQ`v'=mean(IDBNQ`v'_del)
  by year county: egen IDBNO`v'_del=total(DBNO`v') if yjdup<=1
  by year county: egen IDBNO`v'=mean(IDBNO`v'_del)
  gen PDBNQ`v'=IDBNQ`v'/yNQ*100
  gen PDBNO`v'=IDBNO`v'/yNQ*100
}
drop *_del

local varl "white black asian hispanic"
foreach v in `varl' {
	gen DPDBQ`v'=PDBNQ`v'-PDBQ`v'
	gen DPDBO`v'=PDBNO`v'-PDBO`v'
}
	
******************** PANEL A IN TABLE D3 OR D9 IN APPENDIX  ****************
********* EJ perspective of NIMBY*********************************************
label var PDBQwhite "% of waste going to white communities (baseline)"
label var PDBQblack "% of waste going to black communities (baseline)"
label var PDBQasian "% of waste going to Asian communities (baseline)"
label var PDBQhispanic "% of waste going to Hispanic communities (baseline)"
label var DPDBQwhite "change in % of waste going to white communities"
label var DPDBQblack "change in % of waste going to black communities"
label var DPDBQasian "change in % of waste going to Asian communities"
label var DPDBQhispanic "change in % of waste going to Hispanic communities"

di "This is the result of counterfactual (without price adjustment) of scenario:"
di "`s' at county `c'"
di "PANEL A IN TABLE D3 IN APPENDIX D"
di "Or table D9, if this is about Solano County"
*see % of trash originating from a county to affected communities
gsort cdis county
list county qto`c' ADqto`c' ADcdolptc ADcmilptc ADexpq ADimpq ADctotCSptc ///
	PDBQwhite PDBQblack PDBQasian PDBQhispanic ///
	DPDBQwhite DPDBQblack DPDBQasian DPDBQhispanic  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.))
	
} // ending "`s'"=="importBan"

******************************************************************************
************  IMPORT BAN  ********************************
if "`s'"!="importBan" {
**** Calculate baseline level *********
****1. IMPORT DATA OF COUNTERFACTUAL SCENARIO ***********
import excel "$mydir\matlab\output\nimby_counterfactual_`s'_`c'.xlsx", sheet("Sheet1") firstrow clear
keep year quarter county Code Cpr Cq CCS
sort year quarter county Code
tempfile tmp
save `tmp', replace
use nimby_counterfactual, clear 
sort year quarter county Code
merge 1:1 year quarter county Code using `tmp', force
drop _merge
erase `tmp'
**Adjust and rename realprice, dridis, diesel price depending on counterfactual************
gen Nrealprice=realprice
if "`s'"=="importTax" {
replace Nrealprice=Nrealprice*1.10  if localf==0 & desco=="`c'"
}
if "`s'"=="trashTax" {
replace Nrealprice=Nrealprice*1.10
}
*mile tax affects share and flows, not directly on price or distance
gen Ndridis=dridis
*spread values because I drop some observations when doing counterfactuals
bys year quarter county: egen CCS_del=mean(CCS)
drop CCS
rename CCS_del CCS

gen DEL_q=Cq-Bq
gen Nq=q+DEL_q/Bq*q
replace Nq=0 if DEL_q<=1e-2 & q==0
replace Nq=Cq-Bq if DEL_q>1e-2 & q==0
replace Nq=0 if localf==0
gen Dq=Nq-q

******************ECONOMIC VARIABLES *********************
*** Calculate policy variables at current state (with actual q)
keep if year==2010
bys year county: egen havelocalc=total(localf)
replace havelocalc=1 if havelocalc>0 & havelocalc<.
******************** CURRENT STATISTICS. ACTUAL LEVEL *****************
**calculate export amount, import amount, and net import by county
sort year county  // exports
by year county: egen Atcq=total(q)
*export to other county, but within California
by year county: egen Aexpq_del= total(q) if localf==0 & j~="oos" 
by year county: egen Aexpq= mean(Aexpq_del)
replace Aexpq=0 if Aexpq==.
sort year desco // imports
*import by destination county
by year desco: egen Aimpdes_del =total(q) if localf==0 & j~="oos" 
by year desco: egen Aimpdes=mean(Aimpdes_del) // spread values to des cnty level
replace Aimpdes=0 if Aimpdes==.
gen Aimpq_del= Aimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimpq=mean(Aimpq_del) // spread values to county
replace Aimpq=0 if Aimpq==.
drop *_del Aimpdes
gen Anetim= Aimpq - Aexpq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen Arqdes_del=total(q)
gen Arq_del=Arqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Arq=mean(Arq_del)
drop *_del

**payment, total tipping fees
sort year county // exports
gen Adol_del=q*realprice
by year county: egen Acdol=total(Adol_del) // total tipping fees
replace Acdol=0 if Acdol==.
by year county: egen Aexdol_c_del=total(Adol_del) if localf==0 & j~="oos"
by year county: egen Aexdol=mean(Aexdol_c_del)
replace Aexdol=0 if Aexdol==.
sort year desco // imports
by year desco: egen Aimdol_d_del=total(Adol_del) if localf==0 & j~="oos"
by year desco: egen Aimdold=mean(Aimdol_d_del)
replace Aimdold=0 if Aimdold==.
gen Aimdol_c_del=Aimdold if county==desco // spread values from des cnty to county
sort year county
by year county: egen Aimdol=mean(Aimdol_c_del)
replace Aimdol=0 if Aimdol==.
gen Anetimdol=Aimdol-Aexdol
drop *_del
gen Acdolpt=Acdol/Atcq // dollar per ton

**trash miles
gen Acmil_del=q*dridis
sort year county
by year county: egen Acmil=total(Acmil_del) // total trash ton-miles
replace Acmil=0 if Acmil==.
drop *_del
gen Acmilpt=Acmil/Atcq // dollar per ton

******************* SIMULATED/MODEL IMPLIED STATISTICS ****************
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen tcq=total(Bq)
by year county: egen gq_del=total(Bq) if j~="oos"
by year county: egen gq=mean(gq_del)
*export to other county, but within California
by year county: egen expq_del= total(Bq) if localf==0 & j~="oos"
by year county: egen expq= mean(expq_del)
replace expq=0 if expq==.
sort year desco //imports
*import by destination county
by year desco: egen impdes_del =total(Bq) if localf==0 & j~="oos" 
by year desco: egen impdes=mean(impdes_del) // spread values to des cnty level
replace impdes=0 if impdes==.
gen impq_del= impdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen impq=mean(impq_del) // spread values to county
replace impq=0 if impq==.
drop *_del impdes
gen netim= impq - expq // net import by county

**total receiving trash, by county
sort year desco
by year desco: egen rqdes_del=total(Bq)
gen rq_del=rqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen rq=mean(rq_del)
replace rq=0 if rq==.
drop *_del

**payment, total tipping fees
sort year county //exports
gen dol_del=Bq*realprice
by year county: egen cdol=total(dol_del) // total tipping fees
replace cdol=0 if cdol==.
by year county: egen exdol_c_del=total(dol_del) if localf==0 & j~="oos"
by year county: egen exdol=mean(exdol_c_del)
replace exdol=0 if exdol==.
sort year desco //imports
by year desco: egen imdol_d_del=total(dol_del) if localf==0 & j~="oos"
by year desco: egen imdold=mean(imdol_d_del)
replace imdold=0 if imdold==.
gen imdol_c_del=imdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen imdol=mean(imdol_c_del)
replace imdol=0 if imdol==.
gen netimdol=imdol-exdol
gen cdolpt=cdol/tcq // dollar per ton
drop *_del
gen exdolpt=exdol/expq // dollar per ton for exports

*trash miles
gen cmil_del=Bq*dridis
sort year county
by year county: egen cmil=total(cmil_del) // total trash ton-miles
replace cmil=0 if cmil==.  // trash mileage
gen cmilpt=cmil/tcq // dollar per ton

by year county: egen exmil_c_del=total(cmil_del) if localf==0 & j~="oos"
by year county: egen exmil=mean(exmil_c_del)
replace exmil=0 if exmil==.
drop *_del
gen exmilpt=exmil/expq // mile per trip for exports

********************NEW LEVELS *******
**calculate export amounts and import amount using q_tcj
sort year county  // exports
by year county: egen Ntcq=total(Cq)
by year county: egen Ngq_del=total(Cq) if j~="oos"
by year county: egen Ngq=mean(Ngq_del)
by year county: egen Nexpq_del= total(Cq) if localf==0 & j~="oos"
by year county: egen Nexpq= mean(Nexpq_del)
replace Nexpq=0 if Nexpq==.
sort year desco //imports
by year desco: egen Nimpdes_del =total(Cq) if localf==0 & j~="oos" // calculate import by des county
by year desco: egen Nimpdes=mean(Nimpdes_del) // spread values to des cnty level
replace Nimpdes=0 if Nimpdes==.
gen Nimpq_del= Nimpdes if county==desco // spread values from des cnty to county
sort year county
by year county: egen Nimpq=mean(Nimpq_del) // spread values to county
replace Nimpq=0 if Nimpq==.
drop *_del Nimpdes
gen Nnetim= Nimpq - Nexpq

**total receiving trash
sort year desco
by year desco: egen Nrqdes_del=total(Cq)
gen Nrq_del=Nrqdes_del if county==desco // spread values from descnty to county
sort year county
by year county: egen Nrq=mean(Nrq_del)
replace Nrq=0 if Nrq==.
drop *_del

*payment
sort year county //exports
gen Ndol_del=Cq*Nrealprice
by year county: egen Ncdol=total(Ndol_del)
replace Ncdol=0 if Ncdol==.
by year county: egen Nexdol_c_del=total(Ndol_del) if localf==0 & j~="oos"
by year county: egen Nexdol=mean(Nexdol_c_del)
replace Nexdol=0 if Nexdol==.
sort year desco //imports
by year desco: egen Nimdol_d_del=total(Ndol_del) if localf==0 & j~="oos"
by year desco: egen Nimdold=mean(Nimdol_d_del)
replace Nimdold=0 if Nimdold==.
gen Nimdol_c_del=Nimdold if county==desco //spread values from des cnty to county
sort year county
by year county: egen Nimdol=mean(Nimdol_c_del)
replace Nimdol=0 if Nimdol==.
gen Nnetimdol=Nimdol-Nexdol
gen Ncdolpt=Ncdol/Ntcq // dollar per ton
drop *_del
gen Nexdolpt=Nexdol/Nexpq // dollar per ton for exports

*trash miles
gen Ncmil_del=Cq*Ndridis
sort year county
by year county: egen Ncmil=total(Ncmil_del)
replace Ncmil=0 if Ncmil==.
gen Ncmilpt=Ncmil/Ntcq // mile per trip
by year county: egen Nexmil_c_del=total(Ncmil_del) if localf==0 & j~="oos"
by year county: egen Nexmil=mean(Nexmil_c_del)
replace Nexmil=0 if Nexmil==.
drop *_del
gen Nexmilpt=Nexmil/Nexpq // mile per trip for exports

sort year quarter county
by year quarter county: egen cntybq=total(Bq)
by year quarter county: egen cntycq=total(Cq)
gen totCS=CS*cntybq
gen NtotCS=CCS*cntycq
*gen ADtotCS=NtotCS-totCS
sort year county quarter
by year county quarter: gen dup=cond(_N==1,0,_n)
by year county: egen ctotCS_del=total(totCS) if dup<=1
by year county: egen ctotCS=mean(ctotCS_del)
by year county: egen NctotCS_del=total(NtotCS) if  dup<=1
by year county: egen NctotCS=mean(NctotCS_del) 
gen ctotCSpt=ctotCS/tcq // dollar per ton
gen NctotCSpt=NctotCS/Ntcq // dollar per ton
drop *_del
gen expqp = expq*100/tcq // percentage of export
*gen Nexpqp= Nexpqp*100/tcq
gen impqp = impq*100/tcq
******** change in external cost of transportation ******
*Austin2018 estimates external cost of truck trans=2.62-5.86 in 2014 cents per ton-mile
*This means 1.91-4.26 in 2000 cents per ton-mile
gen extran1=cmil*1.91/100 // in dollar
gen Nextran1=Ncmil*1.91/100 // in dollar

*total waste to county of policy
bys year county: egen qto`c'_del=total(Bq) if desco=="`c'"
bys year county: egen qto`c'=mean(qto`c'_del)
replace qto`c'=0 if qto`c'==.
bys year county: egen Nqto`c'_del=total(Cq) if desco=="`c'"
bys year county: egen Nqto`c'=mean(Nqto`c'_del)
replace Nqto`c'=0 if Nqto`c'==. 
drop *_del
replace Nqto`c'=. if qto`c'==0 & Nqto`c'==0
replace qto`c'=. if qto`c'==0 & Nqto`c'==.

**Export to Marin
bys year: egen exto`c'_del=total(Bq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen exto`c'=mean(exto`c'_del) 
bys year: egen Nexto`c'_del=total(Cq) if desco=="`c'" & localf==0 & j~="oos"
bys year: egen Nexto`c'=mean(Nexto`c'_del)

**Total intercounty in CA
bys year: egen interq_del=total(Bq) if localf==0 & j~="oos"
bys year: egen interq=mean(interq_del)
bys year: egen Ninterq_del=total(Cq) if localf==0 & j~="oos"
bys year: egen Ninterq=mean(Ninterq_del)
drop *_del

local varl "rq expq impq cdol cmil ctotCS extran1 qto`c' exto`c' interq"
foreach v in `varl' {
  gen AD`v'=N`v' -`v'
  gen AD`v'p=AD`v'*100/`v'
}

******* change in price at facility level ***************
by year: egen atcq=mean(tcq) if dupc<=1 & havelocalc==1 & county~="`c'"

local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'pt=AD`v'/atcq
}

replace ADextran1=0 if abs(ADcmilpt)<0.0005
replace ADcmilpt=0 if abs(ADcmilpt)<0.0005

**change by county ******
local varl "cdol cmil ctotCS"
foreach v in `varl' {
gen AD`v'ptc=AD`v'/tcq
}

***benefits of reduction in export to break even with the hauling cost ***
*each ycounty has importq impq and cost ctotCS
gen hhvaluec=-ADctotCS/ADimpq
*******###############################################################********
*******************************************************************************
di "This is the result of counterfactual (without price adjustment) of scenario:"
di "`s' in county `c'"

label var exto`c' "import in `c' (baseline)"
label var ADexto`c' "change in import in `c'"
label var interq "intercounty waste in CA"
label var ADinterq "change in intercounty waste in CA"
label var expqp "percentage of export (baseline)"
label var expq "export amount (baseline)"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount (baseline)"
label var ADimpq "change in import amount"
label var cdolpt "tipping fees (baseline)"
label var ADcdolptc "change in tipping fees"
label var cmilpt "mileage (baseline)"
label var ADcmilptc "change in mileage"
label var ADctotCSptc "change in discarding cost"
label var extran1 "external transport cost (baseline)"
label var ADextran1 "change in external transport cost"
label var hhvaluec "household value"
label var expqp "percentage of export (baseline)"
label var expq "export amount"
label var ADexpq "change in export amount"
label var impqp "percentage of import (baseline)"
label var impq "import amount"
label var ADimpq "change in import amount"

gen cpwlat`c'_del=cpwlat if county=="`c'"
egen cpwlat`c'=mean(cpwlat`c'_del)
gen cpwlon`c'_del=cpwlon if county=="`c'"
egen cpwlon`c'=mean(cpwlon`c'_del)
drop *_del

********  TABLE D2 OR D7 IN APPENDIX. IMPORT TAX IN FIXED-PRICE MODEL ********
geodist cpwlat cpwlon cpwlat`c' cpwlon`c', miles gen(cdis)
di "TABLE D2 IN APPENDIX D: PANEL A. IMPORT TAX IN FIXED-PRICE MODEL"
di "Or table D7, if this is about Solano County"
gsort cdis county
list county gq rq ADrq ADexpq ADimpq ///
	ADextran1 ADcdolptc ADcmilptc ADctotCSptc hhvaluec cdis ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.))

*** DISTRIBUTIONAL EFFECTS ***************************************
keep if year==2010

*** percent of trash of origin county to its all within-60miles facilities
sort year county Code
by year county Code: gen yjdup=cond(_N==1,0,_n)
by year county Code: egen yjaq=total(Bq) // receiving q at year-county level
by year county Code: egen yjoq_del=total(Bq) if localf==0
by year county Code: egen yjoq=mean(yjoq_del)
drop *_del
sort year county
by year county: egen yQ=total(Bq)
local varl "white black asian hispanic"
foreach v in `varl' {
  gen DBQ`v'=D_B`v'/D_Bpop*yjaq // /yaq
  gen DBO`v'=D_B`v'/D_Bpop*yjoq // /yoq
  sort year county
  by year county: egen IDBQ`v'_del=total(DBQ`v') if yjdup<=1
  by year county: egen IDBQ`v'=mean(IDBQ`v'_del)
  by year county: egen IDBO`v'_del=total(DBO`v') if yjdup<=1
  by year county: egen IDBO`v'=mean(IDBO`v'_del)
  gen PDBQ`v'=IDBQ`v'/yQ*100
  gen PDBO`v'=IDBO`v'/yQ*100  
}
drop *_del DBQ* DBO*

** new trash distribution
sort year county Code
by year county Code: egen yjaNq=total(Cq) // receiving q at year-county level
by year county Code: egen yjoNq_del=total(Cq) if localf==0
by year county Code: egen yjoNq=mean(yjoNq_del)
drop *_del
sort year county
by year county: egen yNQ=total(Cq)

by year county: egen NqTo0pop_del=total(yjaNq) if yjdup<=1 & D_Bpop==0
by year county: egen NqTo0pop=mean(NqTo0pop_del)
replace NqTo0pop=0 if NqTo0pop==.
gen NqTo0poppc=NqTo0pop/yNQ*100

local varl "white black asian hispanic"
foreach v in `varl' {
	*gen D_B`v'p=D_B`v'/D_Bpop*100
  gen DBNQ`v'=D_B`v'/D_Bpop*yjaNq // /yaNq
  gen DBNO`v'=D_B`v'/D_Bpop*yjoNq // /yoNq
  sort year county
  by year county: egen IDBNQ`v'_del=total(DBNQ`v') if yjdup<=1
  by year county: egen IDBNQ`v'=mean(IDBNQ`v'_del)
  by year county: egen IDBNO`v'_del=total(DBNO`v') if yjdup<=1
  by year county: egen IDBNO`v'=mean(IDBNO`v'_del)
  gen PDBNQ`v'=IDBNQ`v'/yNQ*100
  gen PDBNO`v'=IDBNO`v'/yNQ*100
}
drop *_del

local varl "white black asian hispanic"
foreach v in `varl' {
	gen DPDBQ`v'=PDBNQ`v'-PDBQ`v'
	gen DPDBO`v'=PDBNO`v'-PDBO`v'
}
	
************************* TABLE D4 OR D10 IN APPENDIX  *******************
********* EJ perspective of NIMBY*********************************************
label var PDBQwhite "% of waste going to white communities (baseline)"
label var PDBQblack "% of waste going to black communities (baseline)"
label var PDBQasian "% of waste going to Asian communities (baseline)"
label var PDBQhispanic "% of waste going to Hispanic communities (baseline)"
label var DPDBQwhite "change in % of waste going to white communities"
label var DPDBQblack "change in % of waste going to black communities"
label var DPDBQasian "change in % of waste going to Asian communities"
label var DPDBQhispanic "change in % of waste going to Hispanic communities"

di "This is the result of counterfactual (without price adjustment) of scenario:"
di "`s' at county `c'"
di "PANEL A IN TABLE D4 IN APPENDIX D"
di "Or table D10, if this is about Solano County"
*see % of trash originating from a county to affected communities
gsort cdis county
list county qto`c' ADqto`c' ADcdolptc ADcmilptc ADexpq ADimpq ADctotCSptc ///
	PDBQwhite PDBQblack PDBQasian PDBQhispanic ///
	DPDBQwhite DPDBQblack DPDBQasian DPDBQhispanic  ///
	if year==2010  & dupc<=1 & (abs(ADrq)>0.1 & ADrq<. | (abs(ADctotCS)>0 & abs(ADctotCSptc)>0.0005 & ADctotCSptc<.))

 } // ending "`s'"!="importBan"
  
} // ending foreach s in `scenario'
} // ending foreach c in `cnty'


